package leave.dataaccess.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import leave.dataaccess.DAOFactory;
import leave.dataaccess.LeaveDAO;
import leave.exception.DataAccessException;
import leave.model.Employee;
import leave.model.Leave;
import leave.model.impl.mysql.LeaveImpl;
import leave.status.LeaveStatus;
import leave.util.*;

public class LeaveDAOImpl implements LeaveDAO {

	private Connection FactoryConnection;

	public LeaveDAOImpl(Connection factoryConnection) {
		super();
		FactoryConnection = factoryConnection;
	}

	@Override
	public Leave getNewLeaveApplication() {
		return new LeaveImpl();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see leave.dataaccess.LeaveDAO#getLeaveTransactions(leave.model.Employee)
	 * Get all leave transactions of an employee.
	 */
	@Override
	public Leave[] getLeaveTransactions(Employee employee) throws DataAccessException{
		// TODO Add code here
		ArrayList<Leave> leaveTransaction= new ArrayList<Leave>();
		int employee_id = employee.getEmployeeID();
		String selectsql = "select * from leavedatabase.leave where employee_id="
				+ employee_id + ";";
		try {
			Statement st = FactoryConnection.createStatement();
			ResultSet rsLeave = st.executeQuery(selectsql);
			while (rsLeave.next()) {
				Leave leaveItem = new LeaveImpl();
				leaveItem.setLeaveType(rsLeave.getString("leave_type_id"));
				leaveItem.setStartDate(rsLeave.getDate("start_date"));
				leaveItem.setEndDate(rsLeave.getDate("end_date"));
				leaveItem.setEmployeeComment(rsLeave
						.getString("employee_comment"));
				leaveItem.setWorkDissemination(rsLeave
						.getString("work_dissemination"));
				leaveItem.setContactDetails(rsLeave.getString("contact_details"));
				leaveItem.setStatus(LeaveStatus.valueOf(rsLeave
						.getString("status")));
				leaveItem.setManagerComments(rsLeave
						.getString("manager_comment"));
				leaveItem.setApprovedBy(DAOFactory.getInstance()
						.getEmployeeLeaveDAO()
						.getEmployee(rsLeave.getInt("approved_by")));
				leaveTransaction.add(leaveItem);
			}
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
		return (Leave[])leaveTransaction.toArray();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * leave.dataaccess.LeaveDAO#getPendingLeaveTransactions(leave.model.Employee
	 * ) Get all Leave application of an employee pending for approval (Those
	 * with status "Applied" and "Updated").
	 */
	@Override
	public Leave[] getPendingLeaveTransactions(Employee employee) throws DataAccessException{
		// TODO Add code here
		
		ArrayList<Leave> leaveTransaction= new ArrayList<Leave>();
		int employee_id = employee.getEmployeeID();
		String selectsql = "select * from leavedatabase.leave where employee_id = "
				+ employee_id + "and status in ('Applied', 'Updated');";
		try {
			Statement st = FactoryConnection.createStatement();
			ResultSet rsLeave = st.executeQuery(selectsql);
			while (rsLeave.next()) {
				LeaveImpl leaveItem = new LeaveImpl();
				leaveItem.setLeaveID(rsLeave.getInt("leave_transaction_id"));
				leaveItem.setLeaveType(rsLeave.getString("leave_type_id"));
				leaveItem.setStartDate(rsLeave.getDate("start_date"));
				leaveItem.setEndDate(rsLeave.getDate("end_date"));
				leaveItem.setEmployeeComment(rsLeave
						.getString("employee_comment"));
				leaveItem.setWorkDissemination(rsLeave
						.getString("work_dissemination"));
				leaveItem.setContactDetails(rsLeave.getString("contact_details"));
				leaveItem.setStatus(LeaveStatus.valueOf(rsLeave
						.getString("status")));
				leaveItem.setManagerComments(rsLeave
						.getString("manager_comment"));
				leaveItem.setApprovedBy(DAOFactory.getInstance()
						.getEmployeeLeaveDAO()
						.getEmployee(rsLeave.getInt("approved_by")));
				leaveTransaction.add(leaveItem);
			}
			st.close();
		}
		catch (SQLException e){
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (Leave[])leaveTransaction.toArray();
		
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see leave.dataaccess.LeaveDAO#addNewLeave(leave.model.Leave) Add leave
	 * transaction to the database.
	 */
	@Override
	public void addNewLeave(Leave leaveApplication) throws DataAccessException {
		
		String insertSql= "insert into leavedatabase.leave values('" + leaveApplication.getLeaveType()
		+ "', '" 
		+ DateConvertTo.getSQLDate(leaveApplication.getStartDate()).toString()
		+ "', " + DateConvertTo.getSQLDate(leaveApplication.getEndDate()).toString()    
		+ "', '" 
		+ leaveApplication.getEmployeeComment() 
		+ "', '" 
		+ leaveApplication.getWorkDissemination()
		+ "', '"
		+ leaveApplication.getContactDetails()
		+"', '"
		+ leaveApplication.getStatus()
		+"', '"
		+ leaveApplication.getManagerComments()
		+ "', '"
		+ leaveApplication.getApprovedBy()
		+ "');";
		try
		{
		Statement st= FactoryConnection.createStatement();
		st.executeUpdate(insertSql);
		st.close();
		}
		catch(SQLException e)
		{
			throw new DataAccessException("Unable to carry out transaction.", e);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see leave.dataaccess.LeaveDAO#updateLeave(leave.model.Leave) Update
	 * leave transaction to the database.
	 */
	@Override
	public void updateLeave(Leave leaveApplication) throws SQLException {
		// TODO Add code here
		String updateSql= "UPDATE leave SET description = '"
			+ leaveApplication.getLeaveType()
			+ "', '"
			+ DateConvertTo.getSQLDate(leaveApplication.getStartDate()).toString()
			+"', '"
			+ DateConvertTo.getSQLDate(leaveApplication.getEndDate()).toString()
			+"', '"
			+ leaveApplication.getEmployeeComment()
			+"', '"
			+ leaveApplication.getWorkDissemination()
			+"', '"
			+ leaveApplication.getContactDetails()
			+"', '"
			+ leaveApplication.getStatus()
			+"', '"
			+ leaveApplication.getManagerComments()
			+"', '"
			+ leaveApplication.getApprovedBy()
			+ "');";
			try
			{
			Statement st= FactoryConnection.createStatement();
			st.executeUpdate(updateSql);
			st.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see leave.dataaccess.LeaveDAO#deleteLeave(leave.model.Leave) Remove
	 * leave transaction from the database.
	 */
	@Override
	public void deleteLeave(Leave leaveApplication) throws DataAccessException {
		// TODO Add code here.
		String deleteSql= "Delete from leave where leave_transaction_id = ?"; 
			//+ leaveApplication.getEmployeeid() + ");";
		try
		{
			PreparedStatement st= FactoryConnection.prepareStatement(deleteSql);
			st.setInt(1, leaveApplication.getLeaveID());
			st.executeUpdate();
			st.close();
		}
		catch(Exception e)
		{
			throw new DataAccessException("Unable to carry out transactions.", e);
		}
	}

	
	@Override
	public Leave getLeaveByID(int leaveID) throws Exception {
		String deleteSql= "select * from leave where leave_transaction_id = ?"; 		
	try
	{
		PreparedStatement st= FactoryConnection.prepareStatement(deleteSql);
		st.setInt(1, leaveID);
		ResultSet rs = st.executeQuery();
		Leave leaveItem = new LeaveImpl();
		while (rs.next()) {			
			leaveItem.setLeaveID(rs.getInt("leave_transaction_id"));
			leaveItem.setLeaveType(rs.getString("leave_type_id"));
			leaveItem.setStartDate(rs.getDate("start_date"));
			leaveItem.setEndDate(rs.getDate("end_date"));
			leaveItem.setEmployeeComment(rs
					.getString("employee_comment"));
			leaveItem.setWorkDissemination(rs
					.getString("work_dissemination"));
			leaveItem.setContactDetails(rs.getString("contact_details"));
			leaveItem.setStatus(LeaveStatus.valueOf(rs
					.getString("status")));
			leaveItem.setManagerComments(rs
					.getString("manager_comment"));
			leaveItem.setApprovedBy(DAOFactory.getInstance()
					.getEmployeeLeaveDAO()
					.getEmployee(rs.getInt("approved_by")));			
		}
		st.close();
		return leaveItem;
	}
	catch(Exception e)
	{
		throw new DataAccessException("Unable to carry out transactions.", e);
	}
	}

	@Override
	public void updateLeaveStatus(int leaveID, String status, int approvedEmpID, String comment) throws DataAccessException {
		String updateSql= "update leavedatabase.leave set status = '"
			+ status + "' , approved_by=" + approvedEmpID + ", manager_comment='" + comment +
			"' where leave_transaction_id=" + leaveID + ";";
		try
			{
			Statement st= FactoryConnection.createStatement();
			int rows = st.executeUpdate(updateSql);
			System.out.print("rows updated : " + rows);
			st.close();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DataAccessException("Unable to carry out transaction.", e);
		} 
		
	}

}
